package store.dao;

import store.po.Supplier;
import store.utils.Pager;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SupplierDAO {

    /**
     * 根据参数id查询供应商表t_supplier，将结果封装成Supplier返回
     * @param id
     * @return
     * @throws Exception
     */
    public Supplier findById(int id) throws Exception {
        Supplier s=null;
        Connection con= null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con=DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps=con.prepareStatement("select * from t_supplier where id=? ");
            ps.setInt(1,id);
            rs=ps.executeQuery();
            if (rs.next()){
                s=new Supplier();
                s.setId(rs.getInt("id"));
                s.setName(rs.getString("name"));
                s.setTel(rs.getString("tel"));
                s.setContacts(rs.getString("contacts"));
                s.setInfo(rs.getString("info"));
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:"+e.getMessage());
        }finally {
            if(rs!=null) rs.close();
            if(ps!=null) ps.close();
            if(con!=null) con.close();
        }
        return s;
    }

    /**
     * 根据参数name模糊查询供应商表t_supplier，将结果封装成List返回
     * @param name
     * @return
     * @throws Exception
     */
    public List<Supplier> findSuppliers(String name) throws Exception {
        List<Supplier> list=new ArrayList<>();
        Connection con= null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con=DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps=con.prepareStatement("select * from t_supplier where name like ? ");
            ps.setString(1,"%"+name+"%");
            rs=ps.executeQuery();
            while (rs.next()){
                Supplier s=new Supplier();
                s.setId(rs.getInt("id"));
                s.setName(rs.getString("name"));
                s.setTel(rs.getString("tel"));
                s.setContacts(rs.getString("contacts"));
                s.setInfo(rs.getString("info"));
                list.add(s);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:"+e.getMessage());
        }finally {
            if(rs!=null) rs.close();
            if(ps!=null) ps.close();
            if(con!=null) con.close();
        }
        return list;
    }

    /**
     * 根据参数name模糊查询供应商表t_supplier，对查询结果进行分页，将结果封装成List返回
     * @param name  查询条件
     * @param pager 分页对象
     * @return
     * @throws Exception
     */
    public List<Supplier> findSuppliers(String name, Pager pager) throws Exception {
        List<Supplier> list=new ArrayList<>();
        Connection con= null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con=DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps=con.prepareStatement("select count(id) as total from t_supplier where name like ?");
            ps.setString(1,"%"+name+"%");
            rs=ps.executeQuery();
            if(rs.next()){
                pager.setTotal(rs.getInt("total"));
            }
            ps=con.prepareStatement("select * from t_supplier where name like ? limit ?,?");
            ps.setString(1,"%"+name+"%");
            ps.setInt(2,(pager.getCurrentPage()-1)*pager.getPageSize());
            ps.setInt(3,pager.getPageSize());
            rs=ps.executeQuery();
            while (rs.next()){
                Supplier s=new Supplier();
                s.setId(rs.getInt("id"));
                s.setName(rs.getString("name"));
                s.setTel(rs.getString("tel"));
                s.setContacts(rs.getString("contacts"));
                s.setInfo(rs.getString("info"));
                list.add(s);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:"+e.getMessage());
        }finally {
            if(rs!=null) rs.close();
            if(ps!=null) ps.close();
            if(con!=null) con.close();
        }
        return list;
    }

    /**
     * 将Supplier类型参数s的值保存成t_supplier表中的一条记录
     * @param s
     * @throws Exception
     */
    public void save(Supplier s) throws Exception {
        Connection con= null;
        PreparedStatement ps=null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con=DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps=con.prepareStatement("insert into t_supplier value (null ,?,?,?,?)");
            ps.setString(1,s.getName());
            ps.setString(2,s.getContacts());
            ps.setString(3,s.getTel());
            ps.setString(4,s.getInfo());
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:"+e.getMessage());
        }finally {
            if(ps!=null) ps.close();
            if(con!=null) con.close();
        }
    }

    /**
     * 将s的属性值更新到t_supplier表对应主键的记录中
     * @param s
     * @throws Exception
     */
    public void update(Supplier s) throws Exception {
        Connection con= null;
        PreparedStatement ps=null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con=DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps=con.prepareStatement("update t_supplier set name=?,contacts=?,tel=?,info=? where id=?");
            ps.setString(1,s.getName());
            ps.setString(2,s.getContacts());
            ps.setString(3,s.getTel());
            ps.setString(4,s.getInfo());
            ps.setInt(5,s.getId());
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:"+e.getMessage());
        }finally {
            if(ps!=null) ps.close();
            if(con!=null) con.close();
        }
    }
    /**
     * 根据id删除t_supplier表中对应的记录
     * @param id
     * @throws Exception
     */
    public void delete(int id) throws Exception {
        Connection con= null;
        PreparedStatement ps=null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con=DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps=con.prepareStatement("delete  from t_supplier where id=?");
            ps.setInt(1,id);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:"+e.getMessage());
        }finally {
            if(ps!=null) ps.close();
            if(con!=null) con.close();
        }
    }
}
